﻿/*******************************************************************************
 * Copyright © 2017 FriendStar.Framework 版权所有
 * Author: FZH
 * Description: FriendStar快速开发平台
 * Website：http://www.FriendStar.cn
*********************************************************************************/
using System.Collections.Generic;
using FriendStar.Code;
using System.Data.SqlClient;
using FriendStar.Application.VBaseData;
using System.Text;
using System.Data;
using System;
using System.Linq;

namespace FriendStar.Application.WareHouse
{
    /// <summary>
	/// 设备档案对应的数据应用功能类。
	/// </summary>
    public class ConsignmentApp
    {
        string curUser = OperatorProvider.Provider.GetCurrent().UserName;

        public object GetPrintDetailsData(bool isDefaultDate, bool isArchive, string ids)
        {
            List<Dictionary<string, object>> result = null;

            List<Dictionary<string, object>> pageDataList = null;

            DateTime now = DateTime.Now;

            string sql = @" SELECT  isnull(FHappenComName,'') FHappenComName,
	                                isnull(FCredence,'') FCredence,
	                                isnull(FCurDate,'') FCurDate,
	                                isnull(FDescription_DOC,'') FDescription_DOC,
	                                FDocuNO,
	                                FProductionNO,
	                                FProductionName,
	                                FQuantity_KP,
	                                FQuantity,
	                                FPrice,
	                                isnull(FQuantity,0) * isnull(FPrice,0) FAmount,
	                                FStoreName,
	                                FDescription
		                        FROM
			                        {0}
                WHERE  FDocuNO in({1})", tableName = "V_JSK_12_MX_StoLJKListAcco";

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            pageDataList = new List<Dictionary<string, object>>();

            if (isArchive)
            {
                if (isDefaultDate)
                {
                    tableName = "V_JSK_14_MX_StoLJKListAcco_Mid";
                }
                else
                {
                    tableName = "V_JSK_16_MX_StoLJKListAcco_Arc";
                }
            }

            sql = string.Format(sql, tableName, ids);


            SqlParameter[] pars = new SqlParameter[] {

            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    pageDataList.Add(item);
                }
            }

            var query = pageDataList.GroupBy(entity => entity["FDocuNO"]);

            foreach (IGrouping<object, Dictionary<string, object>> info in query)
            {
                List<Dictionary<string, object>> sl = info.ToList();//分组后的集合 

                Dictionary<string, object> pageItem = new Dictionary<string, object>();

                string[] cols = "FDocuNO,FCredence,FHappenComName,FCurDate,FDescription_DOC".Split(',');

                foreach (string col in cols)
                {
                    pageItem.Add(col, sl[0][col]);
                }

                pageItem.Add("curUser", curUser);

                pageItem.Add("curDate", now.ToString("yyyy-MM-dd"));

                pageItem.Add("curTime", now.ToString("HH:mm:ss"));

                pageItem.Add("dataList", sl);

                result.Add(pageItem);
            }

            return result;
        }

        public string GetLedgerDate(string useUnit, string facNum, string itemDesc, string createBy)
        {
            string result = string.Empty, documentNum = string.Empty;

            string sql = "exec GetDocumentNum '{0}','{1}','{2}','{3}',@DocumentNum out,'临时';select @DocumentNum;";

            sql = string.Format(sql, useUnit, facNum, itemDesc, createBy);

            SqlParameter par = new SqlParameter("@DocumentNum", documentNum);

            result = SqlHelper.ExecuteScalar(SqlHelper.GetConnection(), CommandType.Text, sql, par).ToString();

            return result;
        }

        public object GetFProductionTypies(string FClassNO)
        {
            List<Dictionary<string, object>> result = null;

            string sql = @"SELECT
	                        FTypeNO + ' ' + FTypeName FTypeNOName,
                        FTypeNO,
                        FTypeName
                        FROM
	                        V_BUS_SJ_Dictionary
                        WHERE
	                        FClassNO = @FClassNO
                        ORDER BY
	                        FTypeNO";

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            SqlParameter par = new SqlParameter("@FClassNO", FClassNO);

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, par))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object GetConsignmentSADetails(Pagination pagination, string fAccountDate, string fProductionNO, string fProductionTypeNO, bool isDefaultDate)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @"SELECT
FStockCataID,
	UseUnit,
	UseUnitName,
	FProductionNO,
	FProductionName,
	FImmeQuantity,
	FBookQuantity,
	FPrice,
	FMoney,
	FProCompanyNO,
	FProCompanyName,
	FProductionTypeNO,
	FProductionTypeName,
FAccountDate
FROM
	(
		SELECT
			row_number () OVER (ORDER BY {4}) num,
			*
		FROM
			{3}
	) aa
                where  num > {0} and num <= {1}  {2}", tableName = "V_JSK_21_KC_StoLJKStockCata";

            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();

            if (!isDefaultDate)
            {
                tableName = "V_JSK_22_KC_StoLJKStockCata_Arc";
            }

            if (!string.IsNullOrEmpty(fAccountDate))
            {
                conn.Append(string.Format(" and fAccountDate = '{0}'", fAccountDate));
            }

            if (!string.IsNullOrEmpty(fProductionNO))
            {
                conn.Append(string.Format(" and fProductionNO like '%{0}%'", fProductionNO));
            }

            if (!string.IsNullOrEmpty(fProductionTypeNO))
            {
                conn.Append(string.Format(" and fProductionTypeNO = '{0}'", fProductionTypeNO));
            }
            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName, VBaseData.Common.BuildSortSQL(pagination.sidx, pagination.sord));

            VBaseData.Common.GetRecords(pagination, tableName, conn);

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object GetConsignmentLedgerDetails(Pagination pagination, string fAccountDate, string fProductionNO, string useUnit, bool isDefaultDate)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @" select 
                                *
                            from
                            (
                            SELECT
	                            row_number() over(order by {4}) num,o.*
                            FROM
	                            (SELECT
	                            FDocuNO,
	                            FCredence,
	                            FStoreNO,
	                            FStoreName,
	                            FPrice,
	                            FAmount,
	                            CASE FDocuTypeNO
                            WHEN 'QC' THEN
	                            '期初'
                            WHEN 'JR' THEN
	                            '寄售入库'
                            WHEN 'JC' THEN
	                            '寄售出库'
                            WHEN 'JS' THEN
	                            '废品更换'
                            WHEN 'JP' THEN
	                            '寄售盘存'
                            END AS ZY,
                             FCurDate,
                             FQuantity_QC,
                             FQuantity_RK,
                             FQuantity_CK,
                             FQuantity_BS,
                             FQuantity_PC,
                             FHappenComNO,
                             FHappenComName,
                             FDescription,
							FAuditingDate,
							FAuditingTime,
				            useUnit,
				            fAccountDate,
				            fProductionNO

                            FROM
	                            {3})o
                            )a
                where  num > {0} and num <= {1}  and useUnit = @useUnit {2}", tableName = "V_JSK_14_MX_StoLJKListAcco_Mid";

            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();

            decimal last = 0, qc = 0, rk = 0, ck = 0, fp = 0, pc = 0, jc = 0, a = 0, p = 0;

            if (!isDefaultDate)
            {
                tableName = "V_JSK_16_MX_StoLJKListAcco_Arc";
            }

            if (!string.IsNullOrEmpty(fAccountDate))
            {
                conn.Append(string.Format(" and fAccountDate = '{0}'", fAccountDate));
            }

            if (!string.IsNullOrEmpty(fProductionNO))
            {
                conn.Append(string.Format(" and fProductionNO = '{0}'", fProductionNO));
            }

            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName, VBaseData.Common.BuildSortSQL(pagination.sidx, pagination.sord));

            VBaseData.Common.GetRecords(pagination, tableName, conn);

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@useUnit", useUnit)
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    qc = 0; rk = 0; ck = 0; fp = 0; pc = 0; jc = 0;

                    if (0 == last)
                    {
                        qc = 0;

                        decimal.TryParse(string.Format("{0}", item["FQuantity_QC"]), out qc);
                    }
                    else
                    {
                        qc = last;
                    }
                    //期末结存 = 期初+入库-出库-废品+盘存
                    //FQuantity_RK	FQuantity_CK	FQuantity_BS	FQuantity_FX	FQuantity_PC
                    decimal.TryParse(string.Format("{0}", item["FQuantity_RK"]), out rk);

                    decimal.TryParse(string.Format("{0}", item["FQuantity_CK"]), out ck);

                    decimal.TryParse(string.Format("{0}", item["FQuantity_BS"]), out fp);

                    decimal.TryParse(string.Format("{0}", item["FQuantity_PC"]), out pc);

                    decimal.TryParse(string.Format("{0}", item["FPrice"]), out p);

                    jc = qc + rk - ck - fp + pc;

                    a = p * jc;

                    last = jc;

                    item.Add("QC", Convert.ToInt32(qc));

                    item["FAmount"] = a;

                    item.Add("JC", Convert.ToInt32(jc));

                    result.Add(item);
                }
            }

            return result;
        }

        public object GetConsignmentLedgerStatistics(Pagination pagination, string fAccountDate, string fProductionNO, string useUnit, bool isDefaultDate)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @" select 
                                *
                            from
                            (
                            SELECT
	                            row_number() over(order by {4}) num,o.*
                            FROM
	                            (SELECT
	FCurDate,
max(FDocuNO) FDocuNO,
	FHappenComNO,
	FHappenComName,
	SUM (FQuantity_QC) AS FQuantity_QC,
	SUM (FQuantity_RK) AS FQuantity_RK,
	SUM (FQuantity_CK) AS FQuantity_CK,
	SUM (FQuantity_BS) AS FQuantity_BS,
	SUM (FQuantity_PC) AS FQuantity_PC,
							max(FAuditingDate) FAuditingDate,
							max(FAuditingTime) FAuditingTime,
				      max(useUnit) useUnit,
				      max(fAccountDate) fAccountDate,
				      max(fProductionNO) fProductionNO,
sum(FAmount) FAmount
FROM
	{3}
GROUP by FCurDate,FHappenComNO,FHappenComName,FPrice
)o
                            )a
                where  num > {0} and num <= {1}  and useUnit = @useUnit {2}", tableName = "V_JSK_14_MX_StoLJKListAcco_Mid";

            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();

            decimal last = 0, qc = 0, rk = 0, ck = 0, fp = 0, pc = 0, jc = 0, a = 0, p = 0;

            if (!isDefaultDate)
            {
                tableName = "V_JSK_16_MX_StoLJKListAcco_Arc";
            }

            if (!string.IsNullOrEmpty(fAccountDate))
            {
                conn.Append(string.Format(" and fAccountDate = '{0}'", fAccountDate));
            }

            if (!string.IsNullOrEmpty(fProductionNO))
            {
                conn.Append(string.Format(" and fProductionNO = '{0}'", fProductionNO));
            }

            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName, VBaseData.Common.BuildSortSQL(pagination.sidx, pagination.sord));

            VBaseData.Common.GetRecords(pagination, tableName, conn);

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@useUnit", useUnit)
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    qc = 0; rk = 0; ck = 0; fp = 0; pc = 0; jc = 0;

                    if (0 == last)
                    {
                        qc = 0;

                        decimal.TryParse(string.Format("{0}", item["FQuantity_QC"]), out qc);
                    }
                    else
                    {
                        qc = last;
                    }
                    //期末结存 = 期初+入库-出库+返修-废品+盘存
                    //FQuantity_RK	FQuantity_CK	FQuantity_BS	FQuantity_FX	FQuantity_PC
                    decimal.TryParse(string.Format("{0}", item["FQuantity_RK"]), out rk);

                    decimal.TryParse(string.Format("{0}", item["FQuantity_CK"]), out ck);

                    decimal.TryParse(string.Format("{0}", item["FQuantity_BS"]), out fp);

                    decimal.TryParse(string.Format("{0}", item["FQuantity_PC"]), out pc);

                    decimal.TryParse(string.Format("{0}", item["FPrice"]), out p);

                    jc = qc + rk - ck - fp + pc;

                    a = p * jc;

                    last = jc;

                    item.Add("QC", Convert.ToInt32(qc));

                    item.Add("FAmount", a);

                    item.Add("JC", Convert.ToInt32(jc));

                    result.Add(item);
                }
            }

            return result;
        }

        public object GetHappenComs()
        {
            List<Dictionary<string, object>> result = null;

            string sql = @"SELECT
	                UnitNO + ' ' + UnitShortName UnitNOName,
                UnitNO,
                UnitShortName
                FROM
	                ZSJ_B1_GH_SupplierInformation
                WHERE
	                IsSupplier = '1'
                OR IsProduct = '1'
                OR UnitNO = UseUnit
                ORDER BY
	                UnitNO";

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object GetConsignmentExchangeArchivies(Pagination pagination, string fDocuTypeName, string fDocuNO, string fHappenComNO, string fCurDate, string fProductionNO, string fCredence, string FCurDates)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @"SELECT
FDocuTypeName,
	FDocuNO,
	FHappenComNO,
	FHappenComName,
	FCurDate,
	FProductionNO,
	FProductionName,
	FQuantity_KP,
	FQuantity,
	FPrice,
	FAmount,
	FStoreNO,
	FStoreName,
	FCreateBy,
	FCreateDate,
	FCreateTime,
	FAuditingBy,
	FAuditingDate,
	FAuditingTime,
fCredence
FROM
	(
		SELECT
			row_number () OVER (ORDER BY {4}) num,
			*
		FROM
			{3}
	) aa
                where  num > {0} and num <= {1}  {2}", tableName = "V_JSK_14_MX_StoLJKListAcco_Mid_UNION";

            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();

            if (!string.IsNullOrEmpty(fDocuTypeName))
            {
                conn.Append(string.Format(" and FDocuTypeNo = '{0}'", fDocuTypeName));
            }

            if (!string.IsNullOrEmpty(fDocuNO))
            {
                conn.Append(string.Format(" and fDocuNO like '%{0}%'", fDocuNO));
            }

            if (!string.IsNullOrEmpty(fHappenComNO))
            {
                conn.Append(string.Format(" and fHappenComNO = '{0}'", fHappenComNO));
            }

            if (!string.IsNullOrEmpty(fCurDate) && !string.IsNullOrEmpty(FCurDates))
            {
                conn.Append(string.Format(" and fCurDate >= '{0}' and fCurDate <= '{1}'", fCurDate, FCurDates));
            }

            if (!string.IsNullOrEmpty(fProductionNO))
            {
                conn.Append(string.Format(" and fProductionNO like '%{0}%'", fProductionNO));
            }

            if (!string.IsNullOrEmpty(fCredence))
            {
                conn.Append(string.Format(" and fCredence like '%{0}%'", fCredence));
            }

            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName, VBaseData.Common.BuildSortSQL(pagination.sidx, pagination.sord));

            VBaseData.Common.GetRecords(pagination, tableName, conn);

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object GetHappenComies()
        {
            List<Dictionary<string, object>> result = null;

            string sql = @"SELECT
	                        UnitNO + ' ' + UnitShortName UnitNOName,
	                        UnitNO,
	                        UnitShortName
                        FROM
	                        ZSJ_B1_GH_SupplierInformation
                        WHERE
	                        UnitNO = UseUnit
                        ORDER BY
	                        UnitNO";

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object GetConsignmentInstocks(Pagination pagination, string fAccountDate, string fHappenComNO, bool isDefaultDate)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @" select 
                              	FHappenComNO,
	                        FHappenComName,
	                        FQuantity,
	                        FMoney
                            from
                            (
                            SELECT
	                            row_number() over(order by {4}) num,*
                            FROM
	                            {3}
                            )a
                where  num > {0} and num <= {1}  and  FDocuTypeNo = 'JR' {2}", tableName = "V_JSK_13_ML_StoLJKConAcco_Mid_DWCR";

            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();

            if (!isDefaultDate)
            {
                tableName = "V_JSK_16_MX_StoLJKListAcco_Arc_DWCR";
            }

            if (!string.IsNullOrEmpty(fAccountDate))
            {
                conn.Append(string.Format(" and fAccountDate = '{0}'", fAccountDate));
            }

            if (!string.IsNullOrEmpty(fHappenComNO))
            {
                conn.Append(string.Format(" and fHappenComNO like '%{0}%'", fHappenComNO));
            }

            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName, VBaseData.Common.BuildSortSQL(pagination.sidx, pagination.sord));

            VBaseData.Common.GetRecords(pagination, tableName, conn);

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object GetHappenComes()
        {
            List<Dictionary<string, object>> result = null;

            string sql = @"SELECT
	                            UnitNO + ' ' + UnitShortName UnitNOName,
                            UnitNO,
                            UnitShortName
                            FROM
	                            ZSJ_B1_GH_SupplierInformation
                            WHERE
	                            IsCustomer = '1'
                            ORDER BY
	                            UnitNO";

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object GetConsignmentOutstocks(Pagination pagination, string fAccountDate, string fHappenComNO, bool isDefaultDate)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @" select 
                              	FHappenComNO,
	                        FHappenComName,
	                        FQuantity,
	                        FMoney
                            from
                            (
                            SELECT
	                            row_number() over(order by {4}) num,*
                            FROM
	                            {3}
                            )a
                where  num > {0} and num <= {1}  and  FDocuTypeNo = 'JC' {2}", tableName = "V_JSK_13_ML_StoLJKConAcco_Mid_DWCR";

            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();

            if (!isDefaultDate)
            {
                tableName = "V_JSK_16_MX_StoLJKListAcco_Arc_DWCR";
            }

            if (!string.IsNullOrEmpty(fAccountDate))
            {
                conn.Append(string.Format(" and fAccountDate = '{0}'", fAccountDate));
            }

            if (!string.IsNullOrEmpty(fHappenComNO))
            {
                conn.Append(string.Format(" and fHappenComNO like '%{0}%'", fHappenComNO));
            }

            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName, VBaseData.Common.BuildSortSQL(pagination.sidx, pagination.sord));

            VBaseData.Common.GetRecords(pagination, tableName, conn);

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object GetConsignmentInstockStatistics(Pagination pagination, string fAccountDate, string fHappenComNO, bool isDefaultDate, string fProductionNO)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @" select 
                              	FHappenComNO,
	                        FHappenComName,
	                        FProductionNO,
	                        FProductionName,
	                        Fquantity,
	                        Fprice,
	                        FAmount,
	                        FProCompanyNO,
	                        FProCompanyName
                            from
                            (
                            SELECT
	                            row_number() over(order by {4}) num,*
                            FROM
	                            {3}
                            )a
                where  num > {0} and num <= {1}  and  FDocuTypeNo = 'JR' {2}", tableName = "V_JSK_14_MX_StoLJKListAcco_Mid_LJCR";

            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();

            if (!isDefaultDate)
            {
                tableName = "V_JSK_16_MX_StoLJKListAcco_Arc_LJCR";
            }

            if (!string.IsNullOrEmpty(fAccountDate))
            {
                conn.Append(string.Format(" and fAccountDate = '{0}'", fAccountDate));
            }

            if (!string.IsNullOrEmpty(fHappenComNO))
            {
                conn.Append(string.Format(" and fHappenComNO like '%{0}%'", fHappenComNO));
            }
            if (!string.IsNullOrEmpty(fProductionNO))
            {
                conn.Append(string.Format(" and fProductionNO like '%{0}%'", fProductionNO));
            }
            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName, VBaseData.Common.BuildSortSQL(pagination.sidx, pagination.sord));

            VBaseData.Common.GetRecords(pagination, tableName, conn);

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object GetConsignmentOutstockStatistics(Pagination pagination, string fAccountDate, string fHappenComNO, bool isDefaultDate, string fProductionNO)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @" select 
                            FHappenComNO,
	                        FHappenComName,
	                        FProductionNO,
	                        FProductionName,
	                        Fquantity,
	                        Fprice,
	                        FAmount,
	                        FProCompanyNO,
	                        FProCompanyName
                            from
                            (
                            SELECT
	                            row_number() over(order by {4}) num,*
                            FROM
	                            {3}
                            )a
                where  num > {0} and num <= {1}  and  FDocuTypeNo = 'JC' {2}", tableName = "V_JSK_14_MX_StoLJKListAcco_Mid_LJCR";

            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();

            if (!isDefaultDate)
            {
                tableName = "V_JSK_16_MX_StoLJKListAcco_Arc_LJCR";
            }

            if (!string.IsNullOrEmpty(fAccountDate))
            {
                conn.Append(string.Format(" and fAccountDate = '{0}'", fAccountDate));
            }

            if (!string.IsNullOrEmpty(fHappenComNO))
            {
                conn.Append(string.Format(" and fHappenComNO like '%{0}%'", fHappenComNO));
            }
            if (!string.IsNullOrEmpty(fProductionNO))
            {
                conn.Append(string.Format(" and fProductionNO like '%{0}%'", fProductionNO));
            }
            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName, VBaseData.Common.BuildSortSQL(pagination.sidx, pagination.sord));

            VBaseData.Common.GetRecords(pagination, tableName, conn);

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object GetConsignmentInStockComparies(Pagination pagination, string fCurDate, string sJDH, string lBJH, string rKCY, string fCurDates)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @"SELECT
                RKBH,
	                SJDH,
	                RKRQ,
	                LBJH,
	                LJMC,
	                RKSL,
	                RKDJ,
	                RKDJ*RKSL RKDJ_X_RKSL,

	                CKBH,
	                CKSL,
	                CKRQ,
	                RKSL - CKSL RKSL_CKSL,
	                BSZM1,
	                CZRY1,
	                SRRQ1,
	                SRSJ1
                FROM
	                (
		                SELECT
			                row_number () OVER (ORDER BY {4}) num,
			                *
		                FROM
			                {3}
	                ) aa
                where  num > {0} and num <= {1}  {2}", tableName = "V_JSK_22_CS_CWHZDZB";

            string[] RKCYS = " ,AND RKCY <>0,   AND RKCY =0 ".Split(',');

            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();

            if (!string.IsNullOrEmpty(sJDH))
            {
                conn.Append(string.Format(" and sJDH like '%{0}%'", sJDH));
            }

            if (!string.IsNullOrEmpty(lBJH))
            {
                conn.Append(string.Format(" and lBJH like '%{0}%'", lBJH));
            }

            if (!string.IsNullOrEmpty(rKCY))
            {
                try
                {
                    conn.Append(RKCYS[int.Parse(rKCY)]);
                }
                catch 
                {
                    conn.Append(RKCYS[0]);
                }
            }

            if (!string.IsNullOrEmpty(fCurDate) && !string.IsNullOrEmpty(fCurDates))
            {
                conn.Append(string.Format(" and RKRQ >= '{0}' and RKRQ <= '{1}'", fCurDate, fCurDates));
            }

            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName, VBaseData.Common.BuildSortSQL(pagination.sidx, pagination.sord));

            VBaseData.Common.GetRecords(pagination, tableName, conn);

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object GetConsignmentOutStockComparies(Pagination pagination, string fCurDate, string sJDH, string lBJH, string rKCY, string fCurDates)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @"SELECT
               	SJDH,
	            CKBH,
	            FSDWMC,
	            CKRQ,
	            PZBH,
	            LBJH,
	            LJMC,
	            GHDWMC,
	            CKSL,
	            CKDJ,
	            CKDJ * CKSL CKDJ_X_CKSL,
	            RKBH,
	            RKSL,
	            RKRQ,
	            CKSL - RKSL CKSL_RKSL,
	            BSZM2,
	            CZRY,
	            SRRQ,
	            SRSJ

                FROM
	                (
		                SELECT
			                row_number () OVER (ORDER BY {4}) num,
			                *
		                FROM
			                {3}
	                ) aa
                where  num > {0} and num <= {1}  {2}", tableName = "V_JSK_22_CS_CWHZDZB";

            string[] RKCYS = " ,AND RKCY <>0,   AND RKCY =0 ".Split(',');

            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();

            if (!string.IsNullOrEmpty(sJDH))
            {
                conn.Append(string.Format(" and sJDH lik '%{0}%'", sJDH));
            }

            if (!string.IsNullOrEmpty(lBJH))
            {
                conn.Append(string.Format(" and lBJH like '%{0}%'", lBJH));
            }

            if (!string.IsNullOrEmpty(rKCY))
            {
                try
                {
                    conn.Append(RKCYS[int.Parse(rKCY)]);
                }
                catch
                {
                    conn.Append(RKCYS[0]);
                }
            }

            if (!string.IsNullOrEmpty(fCurDate) && !string.IsNullOrEmpty(fCurDates))
            {
                conn.Append(string.Format(" and RKRQ >= '{0}' and RKRQ <= '{1}'", fCurDate, fCurDates));
            }

            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName, VBaseData.Common.BuildSortSQL(pagination.sidx, pagination.sord));

            VBaseData.Common.GetRecords(pagination, tableName, conn);

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object AddConsignmentCommentIn(string sJDH, string lBJH, string bSZM1)
        {
            int result = 0;

            string sql = "UPDATE  JSK_22_CS_CWHZDZB  SET BSZM1 =@BSZM1  WHERE   SJDH =@SJDH  AND  LBJH =@LBJH";

            SqlParameter[] pars = new SqlParameter[] {
                                                    new SqlParameter("@BSZM1", bSZM1),
                                                    new SqlParameter("@SJDH", sJDH),
                                                    new SqlParameter("@LBJH", lBJH)
                                                };

            result = SqlHelper.ExecuteNonQuery(SqlHelper.GetConnection(), CommandType.Text, sql, pars);

            return result;
        }

        public object AddConsignmentCommentOut(string sJDH, string lBJH, string bSZM2)
        {
            int result = 0;

            string sql = "UPDATE  JSK_22_CS_CWHZDZB  SET BSZM2 =@BSZM2  WHERE   SJDH =@SJDH  AND  LBJH =@LBJH";

            SqlParameter[] pars = new SqlParameter[] {
                                                    new SqlParameter("@BSZM2", bSZM2),
                                                    new SqlParameter("@SJDH", sJDH),
                                                    new SqlParameter("@LBJH", lBJH)
                                                };

            result = SqlHelper.ExecuteNonQuery(SqlHelper.GetConnection(), CommandType.Text, sql, pars);

            return result;
        }

        public object GetConsignmentCateSum(Pagination pagination, string fAccountDate, string fProductionTypeNO, bool isDefaultDate)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @" select 
                           UseUnit,
	                        UseUnitName,
	                        FProductionTypeNO,
	                        FProductionTypeName,
	                        FQuantity_QC,
	                        FMoney_QC,
	                        FQuantity_RK,
	                        FMoney_RK,
	                        FQuantity_CK,
	                        FMoney_CK,
	                        FQuantity_BS,
	                        FMoney_BS,
	                        FQuantity_PC,
	                        FMoney_PC,
	                        FQuantity_JC,
	                        FMoney_JC
                            from
                            (
                            SELECT
	                            row_number() over(order by {4}) num,*
                            FROM
	                            {3}
                            )a
                where  num > {0} and num <= {1} {2}", tableName = "V_JSK_14_MX_StoLJKListAcco_Mid_Type";

            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();

            if (!isDefaultDate)
            {
                tableName = "V_JSK_16_MX_StoLJKListAcco_Arc_Type";
            }

            if (!string.IsNullOrEmpty(fAccountDate))
            {
                conn.Append(string.Format(" and fAccountDate = '{0}'", fAccountDate));
            }

            if (!string.IsNullOrEmpty(fProductionTypeNO))
            {
                conn.Append(string.Format(" and fProductionTypeNO = '{0}'", fProductionTypeNO));
            }

            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName, VBaseData.Common.BuildSortSQL(pagination.sidx, pagination.sord));

            VBaseData.Common.GetRecords(pagination, tableName, conn);

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object GetConsignmentPartsSum(Pagination pagination, string fAccountDate, string fProductionNO, bool isDefaultDate)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @" select 
                                UseUnit,
	                                UseUnitName,
	                                FProductionNO,
	                                FProductionName,
	                                FProductionTypeNO,
	                                FProductionTypeName,
	                                FQuantity_QC,
	                                FMoney_QC,
	                                FQuantity_RK,
	                                FMoney_RK,
	                                FQuantity_CK,
	                                FMoney_CK,
	                                FQuantity_BS,
	                                FMoney_BS,
	                                FQuantity_PC,
	                                FMoney_PC,
	                                FQuantity_JC,
	                                FMoney_JC,
	                                FProCompanyNO,
	                                FProCompanyName
                            from
                            (
                            SELECT
	                            row_number() over(order by {4}) num,*
                            FROM
	                            {3}
                            )a
                where  num > {0} and num <= {1} {2}", tableName = "V_JSK_14_MX_StoLJKListAcco_Mid_LJ";

            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();

            if (!isDefaultDate)
            {
                tableName = "V_JSK_16_MX_StoLJKListAcco_Arc_LJ";
            }

            if (!string.IsNullOrEmpty(fAccountDate))
            {
                conn.Append(string.Format(" and fAccountDate = '{0}'", fAccountDate));
            }

            if (!string.IsNullOrEmpty(fProductionNO))
            {
                conn.Append(string.Format(" and fProductionNO like '%{0}%'", fProductionNO));
            }

            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName, VBaseData.Common.BuildSortSQL(pagination.sidx, pagination.sord));

            VBaseData.Common.GetRecords(pagination, tableName, conn);

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object GetConsignmentreceiveAndDispatch(Pagination pagination, string fAccountDate, string fProductionNO)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @" select 
                            UseUnitName,
	                            FProductionNO,
	                            FProductionName,
	                            JCSL,
	                            QCSL,
	                            RK01,
	                            RK02,
	                            RK03,
	                            RK04,
	                            RK05,
	                            RK06,
	                            RK07,
	                            RK08,
	                            RK09,
	                            RK10,
	                            RK11,
	                            RK12,
	                            RK13,
	                            RK14,
	                            RK15,
	                            RK16,
	                            RK17,
	                            RK18,
	                            RK19,
	                            RK20,
	                            RK21,
	                            RK22,
	                            RK23,
	                            RK24,
	                            RK25,
	                            RK26,
	                            RK27,
	                            RK28,
	                            RK29,
	                            RK30,
	                            RK31,
	                            RKHZ,
	                            RKHZ + QCSL RKHZ_QCSL,
	                            CK01,
	                            CK02,
	                            CK03,
	                            CK04,
	                            CK05,
	                            CK06,
	                            CK07,
	                            CK08,
	                            CK09,
	                            CK10,
	                            CK11,
	                            CK12,
	                            CK13,
	                            CK14,
	                            CK15,
	                            CK16,
	                            CK17,
	                            CK18,
	                            CK19,
	                            CK20,
	                            CK21,
	                            CK22,
	                            CK23,
	                            CK24,
	                            CK25,
	                            CK26,
	                            CK27,
	                            CK28,
	                            CK29,
	                            CK30,
	                            CK31,
	                            FPSL,
	                            pcsl,
	                            FProCompanyNO,
	                            FProCompanyName,
	                            FProductionTypeNO,
	                            FProductionTypeName
                            from
                            (
                            SELECT
	                            row_number() over(order by {4}) num,*
                            FROM
	                            {3}
                            )a
                where  num > {0} and num <= {1} {2}", tableName = "V_JSK_23_BB_StoProStockCata_RB";

            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();

            if (!string.IsNullOrEmpty(fAccountDate))
            {
                conn.Append(string.Format(" and fAccountDate = '{0}'", fAccountDate));
            }

            if (!string.IsNullOrEmpty(fProductionNO))
            {
                conn.Append(string.Format(" and fProductionNO like '%{0}'%", fProductionNO));
            }

            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName, VBaseData.Common.BuildSortSQL(pagination.sidx, pagination.sord));

            VBaseData.Common.GetRecords(pagination, tableName, conn);

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }
    }
}




